﻿
-- ================================================
-- Remarks: 
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Ivy_User_GetByCriteria')
	BEGIN
		DROP  Procedure  Ivy_User_GetByCriteria
	END

GO

CREATE PROCEDURE [dbo].[Ivy_User_GetByCriteria] 
-- =============================================
-- Author:		<ARMV>
-- Create date: <21/092012>
-- Description:	<Ivy_User_GetByCriteria>
-- =============================================
-- Add the parameters for the stored procedure here
	(
				@UserLoginID varchar(50) = NULL,
				@UserPassword varchar(100) = NULL,
				@UserFirstName varchar(50) = NULL,
				@UserLastName varchar(50) = NULL,
				@UserPrimaryEmailID varchar(150) = NULL,
				@UserVerificationCode varchar(50) = NULL,
				@UserIsVerified bit = Null,

				@Sys_DateOfCreation VARCHAR(30) = NULL,
				@Sys_DateOfModification varchar(50) = NULL,
				@Sys_CreatedByID varchar(50)= NULL,
				@Sys_ModifiedByID varchar(50) = NULL,
				@Sys_DeleteType varchar(10) = NULL,
				@Sys_IsDeleted bit = NULL,
				@Sys_OrganizationID varchar(10) = NULL,
				@Sys_InitialPrimaryKeys varchar(250)= NULL,
				@Sys_WorkstationName varchar(50)= NULL

	)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	Declare @SQL AS VARCHAR(MAX)
set @SQL =' SELECT [UserKey],
			[UserLoginID],
			[UserPassword],
			[UserFirstName],
			[UserLastName],
			[UserPrimaryEmailID],
			[UserVerificationCode],
			[UserIsVerified],

			[Sys_DateOfCreation],
			[Sys_DateOfModification],
			[Sys_CreatedByID],
			[Sys_ModifiedByID],
			[Sys_DeleteType],
			[Sys_IsDeleted],
			[Sys_OrganizationID],
			[Sys_InitialPrimaryKeys],
			[Sys_WorkstationName]
	FROM	[User]
	WHERE	1=1'
			
			if @UserLoginID IS NOT NULL
			SET @SQL = @SQL+'AND  UserLoginID ='''+@UserLoginID+''''
				
			if @UserPassword IS NOT NULL
			SET @SQL = @SQL+'AND  UserPassword ='''+@UserPassword+''''
			
			if @UserFirstName IS NOT NULL
			SET @SQL = @SQL+'AND  UserFirstName ='''+@UserFirstName+''''
			
			if @UserLastName IS NOT NULL
			SET @SQL = @SQL+'AND  UserLastName ='''+@UserLastName+''''
			
			if @UserPrimaryEmailID IS NOT NULL
			SET @SQL = @SQL+'AND  UserPrimaryEmailID ='''+@UserPrimaryEmailID+''''
			
			if @UserVerificationCode IS NOT NULL
			SET @SQL = @SQL+'AND  UserVerificationCode ='''+@UserVerificationCode+''''
			
			if @UserIsVerified IS NOT NULL
			SET @SQL = @SQL+'AND UserIsVerified ='''+cast(@UserIsVerified as CHAR)+''''
						
							
			if @Sys_DateOfCreation IS NOT NULL
			SET @SQL = @SQL+'AND  Sys_DateOfCreation ='''+@Sys_DateOfCreation+''''
			
						
			if @Sys_DateOfModification IS NOT NULL
			SET @SQL = @SQL+'AND  Sys_DateOfModification ='''+@Sys_DateOfModification+''''
						
			if @Sys_CreatedByID IS NOT NULL
			SET @SQL = @SQL+'AND  Sys_CreatedByID ='''+@Sys_CreatedByID+''''
						
			if @Sys_ModifiedByID IS NOT NULL
			SET @SQL = @SQL+'AND  Sys_ModifiedByID ='''+@Sys_ModifiedByID+''''
						
			
			if @Sys_DeleteType IS NOT NULL
			SET @SQL = @SQL+'AND Sys_DeleteType ='+@Sys_DeleteType
						
			if @Sys_IsDeleted IS NOT NULL
			SET @SQL = @SQL+'AND Sys_IsDeleted ='''+cast(@Sys_IsDeleted as CHAR)+''''
						
			if @Sys_OrganizationID IS NOT NULL
			SET @SQL = @SQL+'AND Sys_OrganizationID ='+@Sys_OrganizationID
						
			if @Sys_InitialPrimaryKeys IS NOT NULL
			SET @SQL = @SQL+'AND  Sys_InitialPrimaryKeys ='''+@Sys_InitialPrimaryKeys+''''
			
			if @Sys_WorkstationName IS NOT NULL
			SET @SQL = @SQL+'AND  Sys_WorkstationName ='''+@Sys_WorkstationName+''''
			
  exec (@SQl)

END


GO
